﻿using System;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace mfe.bll
{
    public partial class Item
    {
        public override string ToString()
        {
            return _Title;
        }

        public static List<Item> RelatedItemCollection(string ItemGuidID)
        {
            List<Item> retval = new List<Item>();
            try
            {
                mfe.lib.data.Database db = mfe.lib.data.DatabaseFactory.CreateDatabase();
                string sql = @"select i.* from itemrelated ir
                    join item i on ir.RelatedItemGuidID = i.ItemGuidID
                    where ir.ItemGuidID=@ItemGuidID";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "ItemGuidID", DbType.String, ItemGuidID);
                IDataReader reader = db.ExecuteReader(cmd);
                while (reader.Read())
                {
                    Item obj = new Item();
                    _SetupProperties(reader, obj);
                    retval.Add(obj);
                }
                reader.Close();
                reader.Dispose();
            }
            catch (Exception ex)
            {
                mfe.lib.Logger.Log(ex);
            }

            return retval;
        }

        public static List<Item> Search(string query, string itemGuidID, int contentTypeID)
        {
            ///TODO: need to sanitize query passed in
            List<Item> retval = new List<Item>();
            try
            {
                mfe.lib.data.Database db = mfe.lib.data.DatabaseFactory.CreateDatabase();
                string sql = "select ItemGuidID, Title from Item where Title like '%" + query + "%' and ItemGuidID <> @ItemGuidID ";
                if (contentTypeID > 0)
                    sql = string.Concat(sql, " and ContentType=@ContentType" );
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "ItemGuidID", DbType.String, itemGuidID);
                if (contentTypeID > 0)
                    db.AddInParameter(cmd, "ContentType", DbType.Int32, contentTypeID);
                IDataReader reader = db.ExecuteReader(cmd);
                while (reader.Read())
                {
                    Item obj = new Item();
                    _SetupProperties(reader, obj);
                    retval.Add(obj);
                }
                reader.Close();
                reader.Dispose();
            }
            catch (Exception ex)
            {
                mfe.lib.Logger.Log(ex);
            }

            return retval;
        }

        public static bool DeleteCascade(string ItemGuidID)
        {
            try
            {

                mfe.lib.data.Database db = mfe.lib.data.DatabaseFactory.CreateDatabase();
                //delete the item
                DbCommand cmd = db.GetSqlStringCommand("delete from Item where ItemGuidID=@ItemGuidID");
                db.AddInParameter(cmd, "ItemGuidID", DbType.String, ItemGuidID);
                db.ExecuteNonQuery(cmd);
                //delete the photo
                cmd.CommandText = "delete from Photo where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                //delete the related items
                cmd.CommandText = "delete from ItemRelated where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                //delete the item tag
                cmd.CommandText = "delete from ItemTag where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                //delete the detail item
                cmd.CommandText = "delete from ItemPerson where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                cmd.CommandText = "delete from ItemBook where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                cmd.CommandText = "delete from ItemMagazine where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                cmd.CommandText = "delete from ItemWebsite where ItemGuidID=@ItemGuidID";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                mfe.lib.Logger.Log(ex);
                return false;
            }
        }
    }
}
